import type {
  AppointmentsExportRequest,
  AppointmentsExportResponse,
  AppointmentsListRequest,
  AppointmentsListResponse,
} from '@shared/types/ipc'
import { writeFile } from 'node:fs/promises'
import { join } from 'node:path'
import { normalizeAppointmentTime } from '@shared/utils/appointment-time'
// 数据库操作 IPC 处理器
import { ipcMain } from 'electron'
import { getDataPath } from '../config/index'
import { getDatabase } from '../db/index'
import { getLogger } from '../logger/index'
import { getAppointmentService } from '../services/appointment-service'

/**
 * 设置数据库处理器
 *
 * @description 设置数据库处理器，包括查询、导出、创建、更新、删除预约记录
 */
export function setupDatabaseHandlers(): void {
  const logger = getLogger()

  // 将 appointment_management 表映射为历史记录页面需要的结构
  function mapToAppointmentRecord(row: any) {
    const toTs = (d?: Date | null) => (d ? Math.floor(new Date(d).getTime() / 1000) : undefined)
    const statusMap: Record<string, string> = {
      booked: 'confirmed',
      pending: 'pending',
      cancelled: 'cancelled',
      failed: 'failed',
      confirmed: 'confirmed',
    }
    const bookingMode = row.booking_mode === 'manual' ? 'standard' : row.booking_mode

    // 从 raw_response 提取 metrics（可选）
    let durationMs: number | undefined
    let bytesSent: number | undefined
    let bytesReceived: number | undefined
    try {
      if (row.raw_response) {
        const rr = JSON.parse(row.raw_response)
        const m = rr?.metrics
        if (m) {
          durationMs = typeof m.durationMs === 'number' ? m.durationMs : undefined
          bytesSent = typeof m.bytesSent === 'number' ? m.bytesSent : undefined
          bytesReceived = typeof m.bytesReceived === 'number' ? m.bytesReceived : undefined
        }
      }
    }
    catch {}

    return {
      id: row.id,
      // 直传类型标识，便于前端标注
      is_placeholder: !!row.is_placeholder,
      is_monitor: !!row.is_monitor,
      reference_number: row.reference_number || undefined,
      user_passport: row.passport_number,
      user_license: row.license_number,
      user_name: row.full_name,
      user_email: row.email,
      user_phone: row.phone_number || undefined,
      issuing_country: row.issuing_country,
      issuing_authority: row.issuing_authority,
      service_type: row.service_type,
      office_code: row.office_code,
      appointment_date: row.appointment_date || '',
      appointment_time: row.appointment_time || '',
      timeslot_id: row.timeslot_id || undefined,
      status: statusMap[row.appointment_status] || 'pending',
      booking_mode: bookingMode,
      job_id: row.job_id || undefined,
      created_at: toTs(row.created_at) as number,
      updated_at: toTs(row.updated_at) as number,
      completed_at: toTs(row.booked_at),
      error_message: row.error_message || undefined,
      // 衍生指标字段（仅返回给前端展示，不改表结构）
      duration_ms: durationMs,
      traffic_bytes_sent: bytesSent,
      traffic_bytes_received: bytesReceived,
    }
  }

  // 数据库：查询预约记录（迁移到 appointment_management + Prisma）
  ipcMain.handle('db:appointments:list', async (event, request: AppointmentsListRequest): Promise<AppointmentsListResponse> => {
    try {
      void event
      const prisma = getDatabase()

      const where: any = {}
      if (request.filters) {
        const f = request.filters as any
        if (f.status) {
          where.appointment_status = f.status
        }
        if (typeof f.is_monitor === 'boolean') {
          where.is_monitor = f.is_monitor
        }
        if (typeof f.is_placeholder === 'boolean') {
          where.is_placeholder = f.is_placeholder
        }
        if (f.date_range) {
          // 历史记录页按日期范围过滤预约日期（字符串），保留兼容
          where.appointment_date = {
            gte: f.date_range.start,
            lte: f.date_range.end,
          }
        }
        if (f.user_info) {
          const or: any[] = []
          if (f.user_info.passport_number) {
            or.push({ passport_number: { contains: f.user_info.passport_number } })
          }
          if (f.user_info.license_number) {
            or.push({ license_number: { contains: f.user_info.license_number } })
          }
          if (or.length)
            where.OR = or
        }
      }

      // 排序
      const orderBy: any = {}
      if (request.sort?.field) {
        const field = request.sort.field
        const dir = (request.sort.order || 'desc').toLowerCase()
        orderBy[field] = dir === 'asc' ? 'asc' : 'desc'
      }
      else {
        orderBy.created_at = 'desc'
      }

      // 分页
      const take = request.pagination?.limit || 50
      const skip = request.pagination?.offset || 0

      const [total, records] = await Promise.all([
        prisma.appointment_management.count({ where }),
        prisma.appointment_management.findMany({ where, orderBy, skip, take }),
      ])

      const hasMore = skip + records.length < total
      return {
        success: true,
        data: {
          appointments: records.map(r => mapToAppointmentRecord(r)) as any,
          total,
          has_more: hasMore,
        },
      }
    }
    catch (error: any) {
      logger.error('查询预约记录失败:', error)
      return {
        success: false,
        data: { appointments: [], total: 0, has_more: false },
        error: error.message,
      }
    }
  })

  // 数据库：导出预约记录
  ipcMain.handle('db:appointments:export', async (event, request: AppointmentsExportRequest): Promise<AppointmentsExportResponse> => {
    try {
      void event
      const prisma = getDatabase()

      const where: any = {}
      if (request.filters) {
        const f = request.filters as any
        if (f.status)
          where.appointment_status = f.status
        if (f.date_range) {
          where.appointment_date = { gte: f.date_range.start, lte: f.date_range.end }
        }
      }

      const appointments = await prisma.appointment_management.findMany({
        where,
        orderBy: { created_at: 'desc' },
      })

      // 生成导出文件
      const mapped = appointments.map(a => mapToAppointmentRecord(a))
      const filePath = await exportAppointments(mapped, request.format, request.file_path)

      logger.info(`✅ 预约记录导出完成: ${filePath}`, { count: appointments.length })

      return {
        success: true,
        file_path: filePath,
        record_count: appointments.length,
      }
    }
    catch (error: any) {
      logger.error('导出预约记录失败:', error)
      return {
        success: false,
        file_path: '',
        record_count: 0,
        error: error.message,
      }
    }
  })

  // 数据库：创建预约记录
  ipcMain.handle('db:appointments:create', async (event, appointment: any) => {
    try {
      void event
      const appService = getAppointmentService()
      const created = await appService.createAppointment({
        passport_number: appointment.user_passport,
        license_number: appointment.user_license,
        full_name: appointment.user_name,
        email: appointment.user_email,
        phone_number: appointment.user_phone,
        issuing_country: appointment.issuing_country,
        issuing_authority: appointment.issuing_authority,
        service_type: appointment.service_type,
        preferred_dates: appointment.preferred_dates,
        preferred_times: appointment.preferred_times,
        priority: appointment.priority,
        remarks: appointment.remarks,
        office_code: appointment.office_code,
      })
      logger.info(`✅ 预约记录已创建: ${created.sequence_number}`)
      return { success: true, data: created }
    }
    catch (error: any) {
      logger.error('创建预约记录失败:', error)
      return {
        success: false,
        error: error.message,
      }
    }
  })

  // 数据库：更新预约记录
  ipcMain.handle('db:appointments:update', async (event, id: number, updates: any) => {
    try {
      void event
      const appService = getAppointmentService()
      const updated = await appService.updateAppointment(id, updates)
      logger.info(`✅ 预约记录已更新: ${id}`)
      return { success: true, data: updated }
    }
    catch (error: any) {
      logger.error('更新预约记录失败:', error)
      return {
        success: false,
        error: error.message,
      }
    }
  })

  // 数据库：删除预约记录
  ipcMain.handle('db:appointments:delete', async (event, id: number) => {
    try {
      void event
      const appService = getAppointmentService()
      await appService.deleteAppointment(id)
      logger.info(`✅ 预约记录已删除: ${id}`)
      return { success: true, deleted: true }
    }
    catch (error: any) {
      logger.error('删除预约记录失败:', error)
      return {
        success: false,
        error: error.message,
      }
    }
  })
}

// 导出预约记录到文件
async function exportAppointments(appointments: any[], format: 'csv' | 'xlsx', filePath?: string): Promise<string> {
  const timestamp = new Date().toISOString().slice(0, 19).replace(/[:-]/g, '')
  const fileName = filePath || join(getDataPath(), `appointments_${timestamp}.${format}`)

  if (format === 'csv') {
    // 导出为 CSV
    const headers = [
      'ID',
      '参考编号',
      '护照号码',
      '驾照号码',
      '姓名',
      '邮箱',
      '电话',
      '签发国家',
      '签发机关',
      '服务类型',
      '办理地点',
      '预约日期',
      '预约时间',
      '状态',
      '预约模式',
      '创建时间',
      '预约成功时间',
      '错误信息',
    ]

    const rows = appointments.map(apt => [
      apt.id,
      apt.reference_number || '',
      apt.user_passport,
      apt.user_license,
      apt.user_name,
      apt.user_email,
      apt.user_phone || '',
      apt.issuing_country,
      apt.issuing_authority,
      apt.service_type,
      apt.office_code,
      // 预约日期统一 YYYY-MM-DD（原存储可能为 DD-MM-YYYY）
      (function formatDate(d) {
        const s = String(d || '')
        if (/^\d{2}-\d{2}-\d{4}$/.test(s)) {
          const [dd, mm, yyyy] = s.split('-')
          return `${yyyy}-${mm}-${dd}`
        }
        return s
      })(apt.appointment_date),
      // 统一预约时间格式为 HH:MM（移除秒部分）
      normalizeAppointmentTime(apt.appointment_time || ''),
      apt.status,
      apt.booking_mode,
      // 创建时间（本地时区）到秒
      new Date(apt.created_at * 1000).toLocaleString('zh-CN', { hour12: false }).replace(/\//g, '-'),
      // 预约成功时间（若存在）
      (typeof apt.completed_at === 'number' && apt.completed_at > 0)
        ? new Date(apt.completed_at * 1000).toLocaleString('zh-CN', { hour12: false }).replace(/\//g, '-')
        : '',
      apt.error_message || '',
    ])

    const csvContent = [headers, ...rows]
      .map(row => row.map(cell => `"${String(cell).replace(/"/g, '""')}"`).join(','))
      .join('\n')

    await writeFile(fileName, `\uFEFF${csvContent}`, 'utf-8') // 添加 BOM 以支持中文
  }
  else {
    // TODO: 实现 XLSX 导出
    throw new Error('XLSX 导出功能尚未实现')
  }

  return fileName
}
